﻿namespace IPMS.BLL
{
    using IPMS.DAL;
    using System;
    using System.Collections.Generic;
    using System.Configuration;
    using System.Data;
    using System.Data.SqlClient;
    using System.Runtime.CompilerServices;

    public class SearchProject
    {
        private string _Agent;
        private string _Applicant;
        private string _ClientName;
        private string _Country;
        private string _Inventor;
        private DateTime _NextDueDate;
        private string _ProjectClass;
        private string _ProjectCode;
        private string _ProjectGroupId;
        private string _Remark;
        private string _ResponsiblePerson;
        private string _Stage;
        private string _Status;
        private string _Title;
        private string _Type;
        private bool _IsDelete;
        private string _UpdateIP;
        private string _DeleteIP;
        private string _DeleteBy;
        private DateTime? _DeleteDate;
        private string _UpdateBy;
        private DateTime? _UpdateDate;

        public SearchProject() : this(string.Empty, string.Empty, string.Empty, string.Empty, string.Empty, string.Empty, string.Empty, string.Empty, string.Empty, string.Empty, string.Empty)
        {
        }

        public SearchProject(string projectCode, string clientName, string country, string type, string agent, string title, string applicant, string responsiblePerson, string status, string inventor, string projectGroupId)
        {
            this._ProjectCode = projectCode;
            this._ClientName = clientName;
            this._Country = country;
            this._Type = type;
            this._Agent = agent;
            this._Title = title;
            this._Applicant = applicant;
            this._ResponsiblePerson = responsiblePerson;
            this._Status = status;
            this._Inventor = inventor;
            this._ProjectGroupId = projectGroupId;
        }

        public SearchProject(string projectCode, string clientName, string country, string type, string agent, string title, string applicant, string responsiblePerson, string status, string inventor, string Stage, DateTime NextDueDate, string ProjectClass, string ProjectGroupId, string Remark, bool IsDelete, string UpdateIP, string DeleteIP, string DeleteBy, DateTime DeleteDate, string UpdateBy, DateTime UpdateDate)
        {
            this._ProjectCode = projectCode;
            this._ClientName = clientName;
            this._Country = country;
            this._Type = type;
            this._Agent = agent;
            this._Title = title;
            this._Applicant = applicant;
            this._ResponsiblePerson = responsiblePerson;
            this._Status = status;
            this._Inventor = inventor;
            this._Stage = Stage;
            this._NextDueDate = NextDueDate;
            this._ProjectClass = ProjectClass;
            this._ProjectGroupId = ProjectGroupId;
            this._Remark = Remark;
            this._IsDelete = IsDelete;
            this._UpdateIP = UpdateIP;
            this._DeleteIP = DeleteIP;
            this._DeleteBy = DeleteBy;
            this._DeleteDate = DeleteDate;
            this._UpdateBy = UpdateBy;
            this._UpdateDate = UpdateDate;
        }

        public List<SearchProject> GetAllProjects()
        {
            SqlCommand sqlCmd = new SqlCommand();
            DataAccessLayer DAL = new DataAccessLayer();
            string cmdText = "SELECT Project.ProjectCode as [ProjectCode], ISNULL(Project.ClientName, N'') AS Client, ISNULL(Country.EnglishName, N'') AS Country, Project.Type, ";
            cmdText = ((cmdText + "ISNULL(Project.Agent, N'') AS Agent, Project.Title, Project.Applicant, ISNULL(ProjectSupervisor.ResponsiblePerson, N'') as [ResponsiblePerson], ") + "Project.Status, Project.Inventor, Project.ProjectGroupId " + "FROM         Project LEFT OUTER JOIN ") + "Country ON Project.Country = Country.Country LEFT OUTER JOIN " + "ProjectSupervisor ON Project.ProjectCode = ProjectSupervisor.ProjectCode ";
            DAL.SetCommandType(sqlCmd, CommandType.Text, cmdText);
            List<SearchProject> searchResult = new List<SearchProject>();
            this.TExecuteReaderCmd<SearchProject>(sqlCmd, new TGenerateListFromReader<SearchProject>(this.TGenerateSearchListFromReader<SearchProject>), ref searchResult);
            return searchResult;
        }

        public List<SearchProject> GetSearchResult(string sCriteria)
        {
            SqlCommand sqlCmd = new SqlCommand();
            DataAccessLayer DAL = new DataAccessLayer();
            string cmdText = "SELECT Project.ProjectCode as [ProjectCode], ISNULL(Project.ClientName, N'') AS Client, ISNULL(Country.EnglishName, N'') AS Country, Project.Type, ";
            cmdText = (((cmdText + "ISNULL(Project.Agent, N'') AS Agent, Project.Title, Project.Applicant, ISNULL(ProjectSupervisor.ResponsiblePerson, N'') as [ResponsiblePerson], ") + "Project.Status, Project.Inventor, Project.ProjectGroupId " + "FROM         Project LEFT OUTER JOIN ") + "Country ON Project.Country = Country.Country LEFT OUTER JOIN " + "ProjectSupervisor ON Project.ProjectCode = ProjectSupervisor.ProjectCode ") + "WHERE " + sCriteria;
            DAL.SetCommandType(sqlCmd, CommandType.Text, cmdText);
            List<SearchProject> searchResult = new List<SearchProject>();
            this.TExecuteReaderCmd<SearchProject>(sqlCmd, new TGenerateListFromReader<SearchProject>(this.TGenerateSearchListFromReader<SearchProject>), ref searchResult);
            return searchResult;
        }

        public List<SearchProject> GetSearchResult(string ProjectGroup, string Type, int SuperUser, string OwnerGroupID, string UserName)
        {
            SqlCommand sqlCmd = new SqlCommand();
            DataAccessLayer DAL = new DataAccessLayer();
            string cmdText = "_IPMS_GetProjectByTypeByGroupSp";
            DAL.SetCommandType(sqlCmd, CommandType.StoredProcedure, cmdText);
            DAL.AddParamToSQLCmd(sqlCmd, "@ProjectGroupId", SqlDbType.NVarChar, 30, ParameterDirection.Input, ProjectGroup);
            DAL.AddParamToSQLCmd(sqlCmd, "@ProjectType", SqlDbType.NVarChar, 30, ParameterDirection.Input, Type);
            DAL.AddParamToSQLCmd(sqlCmd, "@SuperUser", SqlDbType.TinyInt, 1, ParameterDirection.Input, SuperUser);
            DAL.AddParamToSQLCmd(sqlCmd, "@OwnerGroupID", SqlDbType.NVarChar, 30, ParameterDirection.Input, OwnerGroupID);
            DAL.AddParamToSQLCmd(sqlCmd, "@UserName", SqlDbType.NVarChar, 30, ParameterDirection.Input, UserName);
            List<SearchProject> searchResult = new List<SearchProject>();
            this.TExecuteReaderCmd<SearchProject>(sqlCmd, new TGenerateListFromReader<SearchProject>(this.TGenerateSearchListFromReader2<SearchProject>), ref searchResult);
            return searchResult;
        }

        public List<SearchProject> GetSearchResult(string Project, string Status, string Type, string Agent, string Country, string Client, string ProjectTitle, string Applicant, string Stage, string ResponsiblePerson, DateTime StartingNextDueDate, DateTime EndingNextDueDate, string Inventor, string ProjectClass, int SuperUser, string OwnerGroupID, string ProjectGroup, string UserName)
        {
            SqlCommand sqlCmd = new SqlCommand();
            DataAccessLayer DAL = new DataAccessLayer();
            string cmdText = "GetProjectSearchSp";
            DAL.SetCommandType(sqlCmd, CommandType.StoredProcedure, cmdText);
            DAL.AddParamToSQLCmd(sqlCmd, "@Project", SqlDbType.NVarChar, 30, ParameterDirection.Input, Project);
            DAL.AddParamToSQLCmd(sqlCmd, "@Status", SqlDbType.NVarChar, 30, ParameterDirection.Input, Status);
            DAL.AddParamToSQLCmd(sqlCmd, "@Type", SqlDbType.NVarChar, 30, ParameterDirection.Input, Type);
            DAL.AddParamToSQLCmd(sqlCmd, "@Agent", SqlDbType.NVarChar, 30, ParameterDirection.Input, Agent);
            DAL.AddParamToSQLCmd(sqlCmd, "@Country", SqlDbType.NVarChar, 30, ParameterDirection.Input, Country);
            DAL.AddParamToSQLCmd(sqlCmd, "@Client", SqlDbType.NVarChar, 30, ParameterDirection.Input, Client);
            DAL.AddParamToSQLCmd(sqlCmd, "@ProjectTitle", SqlDbType.NVarChar, 60, ParameterDirection.Input, ProjectTitle);
            DAL.AddParamToSQLCmd(sqlCmd, "@Applicant", SqlDbType.NVarChar, 30, ParameterDirection.Input, Applicant);
            DAL.AddParamToSQLCmd(sqlCmd, "@Stage", SqlDbType.NVarChar, 30, ParameterDirection.Input, Stage);
            DAL.AddParamToSQLCmd(sqlCmd, "@ResponsiblePerson", SqlDbType.NVarChar, 30, ParameterDirection.Input, ResponsiblePerson);
            DAL.AddParamToSQLCmd(sqlCmd, "@StartingNextDueDate", SqlDbType.DateTime, 8, ParameterDirection.Input, (string.Format("{0:MM/dd/yyyy}", StartingNextDueDate) == "01/01/0001") ? "1/1/1753" : string.Format("{0:MM/dd/yyyy}", StartingNextDueDate));
            DAL.AddParamToSQLCmd(sqlCmd, "@EndingNextDueDate", SqlDbType.DateTime, 8, ParameterDirection.Input, (string.Format("{0:MM/dd/yyyy}", EndingNextDueDate) == "01/01/0001") ? "01/01/9999" : string.Format("{0:MM/dd/yyyy}", EndingNextDueDate));
            DAL.AddParamToSQLCmd(sqlCmd, "@ProjectClass", SqlDbType.NVarChar, 30, ParameterDirection.Input, ProjectClass);
            DAL.AddParamToSQLCmd(sqlCmd, "@ProjectGroup", SqlDbType.NVarChar, 30, ParameterDirection.Input, ProjectGroup);
            DAL.AddParamToSQLCmd(sqlCmd, "@Inventor", SqlDbType.NVarChar, 30, ParameterDirection.Input, Inventor);
            DAL.AddParamToSQLCmd(sqlCmd, "@SuperUser", SqlDbType.TinyInt, 1, ParameterDirection.Input, SuperUser);
            DAL.AddParamToSQLCmd(sqlCmd, "@OwnerGroupID", SqlDbType.NVarChar, 30, ParameterDirection.Input, OwnerGroupID);
            DAL.AddParamToSQLCmd(sqlCmd, "@UserName", SqlDbType.NVarChar, 30, ParameterDirection.Input, UserName);
            List<SearchProject> searchResult = new List<SearchProject>();
            this.TExecuteReaderCmd<SearchProject>(sqlCmd, new TGenerateListFromReader<SearchProject>(this.TGenerateSearchListFromReader2<SearchProject>), ref searchResult);
            return searchResult;
        }

        public List<SearchProject> GetSearchAndIsDeleteResult(string Project, string Status, string Type, string Agent, string Country, string Client, string ProjectTitle, string Applicant, string Stage, string ResponsiblePerson, DateTime StartingNextDueDate, DateTime EndingNextDueDate, string Inventor, string ProjectClass, int SuperUser, string OwnerGroupID, string ProjectGroup, string UserName, bool IsDelete)
        {
            SqlCommand sqlCmd = new SqlCommand();
            DataAccessLayer DAL = new DataAccessLayer();
            string cmdText = "GetProjectSearchAndIsDeleteSp";
            DAL.SetCommandType(sqlCmd, CommandType.StoredProcedure, cmdText);
            DAL.AddParamToSQLCmd(sqlCmd, "@Project", SqlDbType.NVarChar, 30, ParameterDirection.Input, Project);
            DAL.AddParamToSQLCmd(sqlCmd, "@Status", SqlDbType.NVarChar, 30, ParameterDirection.Input, Status);
            DAL.AddParamToSQLCmd(sqlCmd, "@Type", SqlDbType.NVarChar, 30, ParameterDirection.Input, Type);
            DAL.AddParamToSQLCmd(sqlCmd, "@Agent", SqlDbType.NVarChar, 30, ParameterDirection.Input, Agent);
            DAL.AddParamToSQLCmd(sqlCmd, "@Country", SqlDbType.NVarChar, 30, ParameterDirection.Input, Country);
            DAL.AddParamToSQLCmd(sqlCmd, "@Client", SqlDbType.NVarChar, 30, ParameterDirection.Input, Client);
            DAL.AddParamToSQLCmd(sqlCmd, "@ProjectTitle", SqlDbType.NVarChar, 60, ParameterDirection.Input, ProjectTitle);
            DAL.AddParamToSQLCmd(sqlCmd, "@Applicant", SqlDbType.NVarChar, 30, ParameterDirection.Input, Applicant);
            DAL.AddParamToSQLCmd(sqlCmd, "@Stage", SqlDbType.NVarChar, 30, ParameterDirection.Input, Stage);
            DAL.AddParamToSQLCmd(sqlCmd, "@ResponsiblePerson", SqlDbType.NVarChar, 30, ParameterDirection.Input, ResponsiblePerson);
            DAL.AddParamToSQLCmd(sqlCmd, "@StartingNextDueDate", SqlDbType.DateTime, 8, ParameterDirection.Input, (string.Format("{0:MM/dd/yyyy}", StartingNextDueDate) == "01/01/0001") ? "1/1/1753" : string.Format("{0:MM/dd/yyyy}", StartingNextDueDate));
            DAL.AddParamToSQLCmd(sqlCmd, "@EndingNextDueDate", SqlDbType.DateTime, 8, ParameterDirection.Input, (string.Format("{0:MM/dd/yyyy}", EndingNextDueDate) == "01/01/0001") ? "01/01/9999" : string.Format("{0:MM/dd/yyyy}", EndingNextDueDate));
            DAL.AddParamToSQLCmd(sqlCmd, "@ProjectClass", SqlDbType.NVarChar, 30, ParameterDirection.Input, ProjectClass);
            DAL.AddParamToSQLCmd(sqlCmd, "@ProjectGroup", SqlDbType.NVarChar, 30, ParameterDirection.Input, ProjectGroup);
            DAL.AddParamToSQLCmd(sqlCmd, "@Inventor", SqlDbType.NVarChar, 30, ParameterDirection.Input, Inventor);
            DAL.AddParamToSQLCmd(sqlCmd, "@SuperUser", SqlDbType.TinyInt, 1, ParameterDirection.Input, SuperUser);
            DAL.AddParamToSQLCmd(sqlCmd, "@OwnerGroupID", SqlDbType.NVarChar, 30, ParameterDirection.Input, OwnerGroupID);
            DAL.AddParamToSQLCmd(sqlCmd, "@UserName", SqlDbType.NVarChar, 30, ParameterDirection.Input, UserName);
            DAL.AddParamToSQLCmd(sqlCmd, "@IsDelete", SqlDbType.Bit, 1, ParameterDirection.Input, IsDelete);
            //DAL.AddParamToSQLCmd(sqlCmd, "@UpdateIP", SqlDbType.NVarChar, 50, ParameterDirection.Input, UpdateIP);
            //DAL.AddParamToSQLCmd(sqlCmd, "@DeleteIP", SqlDbType.NVarChar, 50, ParameterDirection.Input, DeleteIP);
            //DAL.AddParamToSQLCmd(sqlCmd, "@DeleteBy", SqlDbType.NVarChar, 50, ParameterDirection.Input, DeleteBy);
            //DAL.AddParamToSQLCmd(sqlCmd, "@DeleteDate", SqlDbType.DateTime, 8, ParameterDirection.Input, DeleteDate);
            List<SearchProject> searchResult = new List<SearchProject>();
            this.TExecuteReaderCmd<SearchProject>(sqlCmd, new TGenerateListFromReader<SearchProject>(this.TGenerateSearchListFromReader2<SearchProject>), ref searchResult);
            return searchResult;
        }

        private void TExecuteReaderCmd<T>(SqlCommand sqlCmd, TGenerateListFromReader<T> gcfr, ref List<T> List)
        {
            if (this.ConnectionString == string.Empty)
            {
                throw new ArgumentOutOfRangeException("ConnectionString");
            }
            if (sqlCmd == null)
            {
                throw new ArgumentNullException("sqlCmd");
            }
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                sqlCmd.Connection = cn;
                cn.Open();
                gcfr(sqlCmd.ExecuteReader(), ref List);
            }
        }

        private void TGenerateSearchListFromReader<T>(SqlDataReader returnData, ref List<SearchProject> searchResult)
        {
            while (returnData.Read())
            {
                SearchProject searchProject = new SearchProject((string) returnData["ProjectCode"], (string) returnData["Client"], (string) returnData["Country"], (string) returnData["Type"], (string) returnData["Agent"], (string) returnData["Title"], (string) returnData["Applicant"], (string) returnData["ResponsiblePerson"], (string) returnData["Status"], (string) returnData["Inventor"], (string) returnData["ProjectGroupId"]);
                searchResult.Add(searchProject);
            }
        }

        private void TGenerateSearchListFromReader2<T>(SqlDataReader returnData, ref List<SearchProject> searchResult)
        {
            while (returnData.Read())
            {
                SearchProject searchProject = new SearchProject((string)returnData["ProjectCode"], (string)returnData["Client"], (string)returnData["Country"], (string)returnData["Type"], (string)returnData["Agent"], (string)returnData["Title"], (string)returnData["Applicant"], (string)returnData["ResponsiblePerson"], (string)returnData["Status"], (string)returnData["Inventor"], (string)returnData["Stage"], (DateTime)returnData["NextDueDate"], (string)returnData["ProjectClass"], (string)returnData["ProjectGroupId"],
                    (string)returnData["Remark"], (bool)returnData["IsDelete"], (string)returnData["UpdateIP"], (string)returnData["DeleteIP"], (string)returnData["DeleteBy"], (DateTime)returnData["DeleteDate"], (string)returnData["UpdateBy"], (DateTime)returnData["UpdateDate"]);
                searchResult.Add(searchProject);
            }
        }
 
        public string Agent
        {
            get
            {
                return this._Agent;
            }
        }

        public string Applicant
        {
            get
            {
                return this._Applicant;
            }
        }

        public string Client
        {
            get
            {
                return this._ClientName;
            }
        }

        protected string ConnectionString
        {
            get
            {
                if (ConfigurationManager.ConnectionStrings["LocalSqlServer"] == null)
                {
                    throw new NullReferenceException("ConnectionString configuration is invalid.");
                }
                string connectionString = ConfigurationManager.ConnectionStrings["LocalSqlServer"].ConnectionString;
                if (string.IsNullOrEmpty(connectionString))
                {
                    throw new NullReferenceException("ConnectionString configuration is invalid.");
                }
                return connectionString;
            }
        }

        public string Country
        {
            get
            {
                return this._Country;
            }
        }

        public string Inventor
        {
            get
            {
                return this._Inventor;
            }
        }

        public DateTime NextDueDate
        {
            get
            {
                return this._NextDueDate;
            }
        }

        public string ProjectClass
        {
            get
            {
                return this._ProjectClass;
            }
        }

        public string ProjectCode
        {
            get
            {
                return this._ProjectCode;
            }
        }

        public string ProjectGroupId
        {
            get
            {
                return this._ProjectGroupId;
            }
        }

        public string Remark
        {
            get
            {
                return this._Remark;
            }
        }

        public string ResponsiblePerson
        {
            get
            {
                return this._ResponsiblePerson;
            }
        }

        public string Stage
        {
            get
            {
                return this._Stage;
            }
        }

        public string Status
        {
            get
            {
                return this._Status;
            }
        }

        public string Title
        {
            get
            {
                return this._Title;
            }
        }

        public string Type
        {
            get
            {
                return this._Type;
            }
        }

        public bool IsDelete
        {
            get
            {
                return this._IsDelete;
            }
        }

        public string UpdateIP
        {
            get
            {
                return this._UpdateIP;
            }
        }

        public string DeleteIP
        {
            get
            {
                return this._DeleteIP;
            }
        }

        public string DeleteBy
        {
            get
            {
                return this._DeleteBy;
            }
        }

        public DateTime? DeleteDate
        {
            get
            {
                return this._DeleteDate;
            }
        }

        public string UpdateBy
        {
            get
            {
                return this._UpdateBy;
            }
        }

        public DateTime? UpdateDate
        {
            get
            {
                return this._UpdateDate;
            }
        }

        private delegate void TGenerateListFromReader<T>(SqlDataReader returnData, ref List<T> tempList);
    }
}

